package dao;

import pojo.Student;
import utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDAOImpl {

    // 添加学生
    public int addStudent(Student student) throws SQLException {
        String sql = "INSERT INTO students (name, age, gender, contact_number, avatar_url, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)";
        return (int) JDBCUtils.execute(sql, student.getName(), student.getAge(), student.getGender(), student.getContactNumber(), student.getAvatarUrl(), student.getCreatedAt(), student.getUpdatedAt());
    }

    // 更新学生信息
    public int updateStudent(Student student) throws SQLException {
        String sql = "UPDATE students SET name = ?, age = ?, gender = ?, contact_number = ?, avatar_url = ?, updated_at = ? WHERE student_id = ?";
        return (int) JDBCUtils.execute(sql, student.getName(), student.getAge(), student.getGender(), student.getContactNumber(), student.getAvatarUrl(), student.getUpdatedAt(), student.getStudentId());
    }

    // 删除学生
    public int deleteStudent(int studentId) throws SQLException {
        String sql = "DELETE FROM students WHERE student_id = ?";
        return (int) JDBCUtils.execute(sql, studentId);
    }

    // 通过ID获取学生
    public Student getStudentById(int studentId) throws SQLException {
        String sql = "SELECT * FROM students WHERE student_id = ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, studentId);
        if (resultSet.next()) {
            return mapRowToStudent(resultSet);
        }
        return null;
    }

    // 获取所有学生
    public List<Student> getAllStudents() throws SQLException {
        String sql = "SELECT * FROM students";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        List<Student> students = new ArrayList<>();
        while (resultSet.next()) {
            students.add(mapRowToStudent(resultSet));
        }
        return students;
    }

    // 分页获取学生
    public List<Student> getStudentsByPage(int page, int pageSize) throws SQLException {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT * FROM students LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, offset, pageSize);
        List<Student> students = new ArrayList<>();
        while (resultSet.next()) {
            students.add(mapRowToStudent(resultSet));
        }
        return students;
    }
    // 获取总学生数量
    public int getTotalStudents() throws SQLException {
        String sql = "SELECT COUNT(*) AS total FROM students";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        if (resultSet.next()) {
            return resultSet.getInt("total");
        }
        return 0;
    }
    // 获取模糊搜索学生的总数量
    public int getTotalStudentsByName(String name) throws SQLException {
        String sql = "SELECT COUNT(*) AS total FROM students WHERE name LIKE ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + name + "%");
        if (resultSet.next()) {
            return resultSet.getInt("total");
        }
        return 0;
    }

    // 根据名字模糊搜索学生并分页
    public List<Student> searchStudentsByName(String name, int page, int pageSize) throws SQLException {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT * FROM students WHERE name LIKE ? LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + name + "%", offset, pageSize);
        List<Student> students = new ArrayList<>();
        while (resultSet.next()) {
            students.add(mapRowToStudent(resultSet));
        }
        return students;
    }

    // 将结果集的一行映射为Student对象
    private Student mapRowToStudent(ResultSet resultSet) throws SQLException {
        Student student = new Student();
        student.setStudentId(resultSet.getInt("student_id"));
        student.setName(resultSet.getString("name"));
        student.setAge(resultSet.getInt("age"));
        student.setGender(resultSet.getString("gender"));
        student.setContactNumber(resultSet.getString("contact_number"));
        student.setAvatarUrl(resultSet.getString("avatar_url"));
        student.setCreatedAt(resultSet.getTimestamp("created_at").toLocalDateTime());
        student.setUpdatedAt(resultSet.getTimestamp("updated_at").toLocalDateTime());
        return student;
    }

    public static void main(String[] args) throws SQLException {
        System.out.println(new StudentDAOImpl().getAllStudents());
    }
}
